package com.yunhe.authority.repository.authority.impl;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.yunhe.authority.domain.authority.EmployeeUser;
import com.yunhe.authority.domain.authority.ProjectUser;
import com.yunhe.authority.domain.authority.User;
import com.yunhe.authority.service.authority.UserService;
import com.yunhe.common.model.PageParam;
import com.yunhe.common.util.StringUtil;
import org.neo4j.ogm.model.Result;
import org.neo4j.ogm.session.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import com.yunhe.common.util.CollectionUtil;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @ClassName UserDao
 * @Description 用户自定义查询Dao
 * @Author HeTao
 * @Date 2021/2/23 14:12
 * @Version 1.0
 **/
@Repository
public class UserDao {

    @Autowired
    private Session session;

    @Autowired
    private UserService userService;

    public Page<User> findUsers(String phone, String title,
                                    String mail, String activity,
                                    String createStartTime, String createEndTime,
                                    String creator, String lastLoginStartTime,
                                    String lastLoginEndTime, String lastLoginSystem,
                                    PageParam pageParam) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        cql.append("MATCH (u:User) WHERE 1=1 and u.admin <> true ");
        //按手机号精确查询
        if(!StringUtil.isEmpty(phone)){
            cql.append(" AND u.phone='"+phone+"'");
        }
        //按姓名模糊查询
        if(!StringUtil.isEmpty(title)){
            cql.append(" AND u.title=~'.*"+title+".*'");
        }
        //按邮箱精确查询
        if(!StringUtil.isEmpty(mail)){
            cql.append(" AND u.mail='"+mail+"'");
        }
        //根据状态查询
        if(!StringUtil.isEmpty(activity)){
            cql.append(" AND u.activity='"+activity+"'");
        }
        //根据创建时间查询
        if(!StringUtil.isEmpty(createStartTime)){
            cql.append(" AND u.createTime >='"+createStartTime+"'");
        }
        if(!StringUtil.isEmpty(createEndTime)){
            cql.append(" AND u.createTime <='"+createEndTime+"'");
        }
        if(!StringUtil.isEmpty(creator)){
            cql.append(" AND u.creator=~'.*"+creator+".*'");
        }
        if(!StringUtil.isEmpty(lastLoginStartTime)){
            cql.append(" AND u.lastLoginTime >='"+lastLoginStartTime+"'");
        }
        if(!StringUtil.isEmpty(lastLoginEndTime)){
            cql.append(" AND u.lastLoginTime <='"+lastLoginEndTime+"'");
        }
        if(!StringUtil.isEmpty(lastLoginSystem)){
            cql.append(" AND u.lastLoginSystem='"+lastLoginSystem+"'");
        }
        //拼接返回值和Limit部分
        int skip=(pageParam.getPage()-1)*pageParam.getSize();
        String queryEnd=" RETURN u  order by u.createTime desc SKIP "+skip+" LIMIT "+pageParam.getSize();
        String countEnd=" RETURN count(u)";


        //分页数据查询结果
        Iterable<User> users=session.query(User.class,cql.toString()+queryEnd, Maps.newHashMap());
        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());

        //封装为分页类
        List<User> content= Lists.newArrayList(users);
        Pageable pageable= PageRequest.of(pageParam.getPage()-1,pageParam.getSize());
        Page<User> page=new PageImpl<User>(content,pageable,total);
        return page;
    }

    public Page<ProjectUser> getStationUser(Long id, String keywords, PageParam pageParam) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        cql.append("MATCH (u:User)-[:HAS_STATION]->(s:Station) WHERE 1=1 and u.admin <> true ");
        //按用户名精确查询
        cql.append(" AND ID(u)="+id);
        if(!StringUtil.isEmpty(keywords)){
            cql.append(" AND (u.name=~'.*"+keywords+".*'");
            cql.append(" or u.title=~'.*"+keywords+".*')");
        }
        //按姓名模糊查询
//        if(!StringUtil.isEmpty(title)){
//            cql.append(" AND u.title=~'.*"+title+".*'");
//        }
        //拼接返回值和Limit部分
        int skip=(pageParam.getPage()-1)*pageParam.getSize();
        String queryEnd=" RETURN ID(u) as userId,u.title as title,u.name as name,s.enterpriseFullName as projectName," +
                "ID(s) as stationId,u.createTime as createTime,u.updateTime as  updateTime" +
                "  order by u.createTime desc  SKIP "+skip+" LIMIT "+pageParam.getSize();
        String countEnd=" RETURN count(u)";
        //分页数据查询结果
        Result result=session.query(cql.toString()+queryEnd, Maps.newHashMap());
        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());
        //封装为分页类
        List<ProjectUser> users = new ArrayList<>();
        if(result != null){
            for (Map<String, Object> map : result) {
                ProjectUser projectUser = JSONObject.parseObject(JSONObject.toJSONString(map), ProjectUser.class);
                users.add(projectUser);
            }
        }
        Page<ProjectUser> page = null;
        if(!CollectionUtil.isEmpty(users)){
            List<ProjectUser> content= Lists.newArrayList(users);
            Pageable pageable= PageRequest.of(pageParam.getPage()-1,pageParam.getSize());
            page=new PageImpl<ProjectUser>(content,pageable,total);
        }
        return page;
    }

    public Page<ProjectUser> getFirmUser(Long id, String keywords, PageParam pageParam) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        cql.append("MATCH (u:User)-[:FIRM_OF]->(f:Firm) WHERE 1=1 and u.admin <> true ");
        //按用户名精确查询
        cql.append(" AND ID(u)="+id);
        if(!StringUtil.isEmpty(keywords)){
            cql.append(" AND (u.name=~'.*"+keywords+".*'");
            cql.append(" or u.title=~'.*"+keywords+".*')");
        }
        //按姓名模糊查询
//        if(!StringUtil.isEmpty(title)){
//            cql.append(" AND u.title=~'.*"+title+".*'");
//        }
        //拼接返回值和Limit部分
        int skip=(pageParam.getPage()-1)*pageParam.getSize();
        String queryEnd=" RETURN ID(u) as userId,u.title as title,u.name as name,f.enterpriseFullName as projectName,ID(f) as firmId," +
                "u.createTime as createTime,u.updateTime as updateTime" +
                " order by u.createTime desc  SKIP "+skip+" LIMIT "+pageParam.getSize();
        String countEnd=" RETURN count(u)";
        //分页数据查询结果
        Result result=session.query(cql.toString()+queryEnd, Maps.newHashMap());
        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());
        //封装为分页类
        List<ProjectUser> users = new ArrayList<>();
        if(result != null){
            for (Map<String, Object> map : result) {
                ProjectUser projectUser = JSONObject.parseObject(JSONObject.toJSONString(map), ProjectUser.class);
                users.add(projectUser);
            }
        }
        Page<ProjectUser> page = null;
        if(!CollectionUtil.isEmpty(users)){
            List<ProjectUser> content= Lists.newArrayList(users);
            Pageable pageable= PageRequest.of(pageParam.getPage()-1,pageParam.getSize());
            page=new PageImpl<ProjectUser>(content,pageable,total);
        }
        return page;
    }

    public Page<EmployeeUser> getFirmUserByCondition(Long firmId, String roleTitle, PageParam pageParam) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        cql.append("match(f:Firm)<-[sr:FRIM_OF]-(u:User)-[:HAS_ROLE{firmId:"+firmId+
                "}]->(r:Role) where 1=1 ");
        //按角色名精确查询
        cql.append("AND ID(f)="+firmId);
        if(!StringUtil.isEmpty(roleTitle)){
            cql.append(" AND r.title=~'"+roleTitle+"'");
        }
        //拼接返回值和Limit部分
        int skip=(pageParam.getPage()-1)*pageParam.getSize();
        String queryEnd=" RETURN DISTINCT ID(u) as userId,u.title as title,u.name as name,u.phone as phone,ID(f) as firmId," +
                "u.createTime as createTime,u.creator as creator,sr.activity as activity" +
                "  order by u.createTime desc  SKIP "+skip+" LIMIT "+pageParam.getSize() ;
        String countEnd=" RETURN count(DISTINCT ID(u))";
        //分页数据查询结果
        Result result=session.query(cql.toString()+queryEnd, Maps.newHashMap());
        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());
        //封装为分页类
        List<EmployeeUser> users = new ArrayList<>();
        if(result != null){
            for (Map<String, Object> map : result) {
                EmployeeUser projectUser = JSONObject.parseObject(JSONObject.toJSONString(map), EmployeeUser.class);
                users.add(projectUser);
            }
        }
        Page<EmployeeUser> page = null;
        if(!CollectionUtil.isEmpty(users)){
            List<EmployeeUser> content= Lists.newArrayList(users);
            Pageable pageable= PageRequest.of(pageParam.getPage()-1,pageParam.getSize());
            page=new PageImpl<EmployeeUser>(content,pageable,total);
        }
        return page;
    }

    public Page<EmployeeUser> getStationUserByCondition(Long stationId, String roleType, PageParam pageParam) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        cql.append("match(s:Station)<-[sr:HAS_STATION]-(u:User)-[:HAS_ROLE{stationId:"+stationId+
                "}]->(r:Role) where 1=1  ");
        //按角色名精确查询
        cql.append("AND ID(s)="+stationId);
        if(!StringUtil.isEmpty(roleType)){
            roleType = ".*"+roleType+".*";
            cql.append(" AND r.title=~'"+roleType+"'");
        }
        //拼接返回值和Limit部分
        int skip=(pageParam.getPage()-1)*pageParam.getSize();
        String queryEnd=" RETURN DISTINCT ID(u) as userId,u.title as title,u.name as name,u.phone as phone,ID(s) as stationId," +
                "u.createTime as createTime,u.creator as creator,sr.activity as activity" +
                " order by u.createTime desc  SKIP "+skip+" LIMIT "+pageParam.getSize() ;
        String countEnd=" RETURN count(DISTINCT ID(u))";
        //分页数据查询结果
        Result result=session.query(cql.toString()+queryEnd, Maps.newHashMap());
        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());
        //封装为分页类
        List<EmployeeUser> users = new ArrayList<>();
        if(result != null){
            for (Map<String, Object> map : result) {
                EmployeeUser projectUser = JSONObject.parseObject(JSONObject.toJSONString(map), EmployeeUser.class);
                users.add(projectUser);
            }
        }
        Page<EmployeeUser> page = null;
        if(!CollectionUtil.isEmpty(users)){
            List<EmployeeUser> content= Lists.newArrayList(users);
            Pageable pageable= PageRequest.of(pageParam.getPage()-1,pageParam.getSize());
            page=new PageImpl<EmployeeUser>(content,pageable,total);
        }
        return page;
    }

    public EmployeeUser getFirmUserById(Long id, Long firmId) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        cql.append("match(f:Firm)<-[sr:FIRM_OF]-(u:User)-[:HAS_ROLE{firmId:"+firmId+
                "}]->(r:Role) where 1=1  ");
        cql.append("AND ID(f)="+firmId);
        cql.append(" AND ID(u)="+id);
        String queryEnd=" RETURN DISTINCT ID(u) as userId,u.title as title,u.name as name,u.phone as phone,u.mail as mail,u.sex as sex,ID(f) as firmId," +
                "u.createTime as createTime,u.creator as creator,sr.activity as activity,sr.remark as remark limit 1";
        //数据查询结果
        Result result=session.query(cql.toString()+queryEnd, Maps.newHashMap());
        //封装为对象
        EmployeeUser user = new EmployeeUser();
        if(result != null){
            for (Map<String, Object> map : result) {
                user = JSONObject.parseObject(JSONObject.toJSONString(map), EmployeeUser.class);
            }
        }
        return user;
    }

    public EmployeeUser getSationUserById(Long id, Long stationId) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        cql.append("match(s:Station)<-[sr:HAS_STATION]-(u:User)-[:HAS_ROLE{stationId:"+stationId+
                "}]->(r:Role) where 1=1  ");
        cql.append("AND ID(s)="+stationId);
        cql.append(" AND ID(u)="+id);
        String queryEnd=" RETURN DISTINCT ID(u) as userId,u.title as title,u.name as name,u.phone as phone,u.mail as mail,u.sex as sex,ID(s) as stationId," +
                "u.createTime as createTime,u.creator as creator,sr.activity as activity,sr.remark as remark limit 1";
        //数据查询结果
        Result result=session.query(cql.toString()+queryEnd, Maps.newHashMap());
        //封装为对象
        EmployeeUser user = new EmployeeUser();
        if(result != null){
            for (Map<String, Object> map : result) {
                user = JSONObject.parseObject(JSONObject.toJSONString(map), EmployeeUser.class);
            }
        }
        return user;
    }

    public Page<User> findUsersById(Long id,
                                    String keywords,
                                    PageParam pageParam) {
        StringBuffer cql = new StringBuffer();
        //拼接基本语句
        cql.append("MATCH(u:User)-[:HAS_ROLE]->(r:Role) WHERE ID(r) = " + id + " ");
        if(!StringUtil.isEmpty(keywords)){
            cql.append(" AND (u.name=~'.*" + keywords + ".*' " );
            cql.append(" or u.title=~'.*" + keywords + ".*') ");
        }

        //拼接返回值和Limit部分
        int skip=(pageParam.getPage()-1)*pageParam.getSize();
        String queryEnd=" RETURN u order by u.createTime desc SKIP "+skip+" LIMIT "+pageParam.getSize();
        String countEnd=" RETURN count(u) ";


        //分页数据查询结果
        Iterable<User> users=session.query(User.class,cql.toString()+queryEnd, Maps.newHashMap());
        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());

        //封装为分页类
        List<User> content= Lists.newArrayList(users);
        Pageable pageable= PageRequest.of(pageParam.getPage()-1,pageParam.getSize());
        Page<User> page=new PageImpl<User>(content,pageable,total);
        return page;
    }

    public Page<EmployeeUser> getUserByStationIdOrFirmId(Long id, Long stationId, Long firmId, String keywords, PageParam pageParam) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        if (stationId != null) {
            cql.append("match(s:Station)<-[sr:HAS_STATION]-(u:User)-[:HAS_ROLE{stationId:"+stationId+"}]->(r:Role) where 1=1  ");
            cql.append(" AND ID(s)="+stationId);
        }else if (firmId != null) {
            cql.append("match(f:Firm)<-[sr:FIRM_OF]-(u:User)-[:HAS_ROLE{firmId:"+firmId+"}]->(r:Role) where 1=1  ");
            cql.append(" AND ID(f)="+firmId);
        } else {
            cql.append("match(u:User)-[:HAS_ROLE]->(r:Role) where 1=1  ");
        }
        cql.append(" AND ID(r)="+id);

        if(!StringUtil.isEmpty(keywords)){
            cql.append(" AND (u.name=~'.*" + keywords + ".*' " );
            cql.append(" or u.title=~'.*" + keywords + ".*') ");
        }

        //拼接返回值和Limit部分
        int skip=(pageParam.getPage()-1)*pageParam.getSize();
        String queryEnd=" RETURN DISTINCT ID(u) as userId,u.title as title,u.name as name,u.phone as phone,u.mail as mail,u.sex as sex," +
                    "u.createTime as createTime,u.creator as creator,u.updateTime as updateTime ,u.activity as activity order by u.createTime desc SKIP "+skip+" LIMIT "+pageParam.getSize();

        String countEnd=" RETURN count(u) ";

        //分页数据查询结果
        Result result=session.query(cql.toString()+queryEnd, Maps.newHashMap());
        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());
        //封装为分页类
        List<EmployeeUser> users = new ArrayList<>();
        if(result != null){
            for (Map<String, Object> map : result) {
                EmployeeUser projectUser = JSONObject.parseObject(JSONObject.toJSONString(map), EmployeeUser.class);
                users.add(projectUser);
            }
        }
        Page<EmployeeUser> page = new PageImpl<>(new ArrayList<>());
        if(!CollectionUtil.isEmpty(users)){
            List<EmployeeUser> content= Lists.newArrayList(users);
            Pageable pageable= PageRequest.of(pageParam.getPage()-1,pageParam.getSize());
            page=new PageImpl<EmployeeUser>(content,pageable,total);
        }
        return page;
    }

    public Long calculateUserByStationIdOrFirmId(Long id, Long stationId, Long firmId) {
        StringBuffer cql=new StringBuffer();
        //拼接基本语句
        if (stationId != null) {
            cql.append("match(s:Station)<-[sr:HAS_STATION]-(u:User)-[:HAS_ROLE{stationId:"+stationId+"}]->(r:Role) where 1=1  ");
            cql.append(" AND ID(s)="+stationId);
        }else if (firmId != null) {
            cql.append("match(f:Firm)<-[sr:FIRM_OF]-(u:User)-[:HAS_ROLE{firmId:"+firmId+"}]->(r:Role) where 1=1  ");
            cql.append(" AND ID(f)="+firmId);
        } else {
            //查询后台用户，用户模型发生变化
            cql.append("match(u:BackUser)-[:HAS_ROLE]->(r:Role) where 1=1  ");
        }
        cql.append(" AND ID(r)="+id);


        String countEnd=" RETURN count(u) ";

        //查询总数
        Integer total=session.queryForObject(Integer.class,cql.toString()+countEnd, Maps.newHashMap());
        return Long.valueOf(total);
    }
}
